﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.IO;
using System.Text;
using System.Data.OracleClient;
using Maticsoft.DBUtility;


namespace Xiaov.SmartWeb.Application.TZ.WP
{
  public partial class WPSJTZ : System.Web.UI.Page
  {
    ExcelHelper helpExcel = new ExcelHelper();
    protected void Page_Load(object sender, EventArgs e)
    {
      Response.Write("<div id='mydiv' >");
      Response.Write("_");
      Response.Write("</div>");
      Response.Write("<script>mydiv.innerText = '';</script>");
      Response.Write("<script language=javascript>;");
      Response.Write("var dots = 0;var dotmax = 10;function ShowWait()");
      Response.Write("{var output; output = '正在装载页面';dots++;if(dots>=dotmax)dots=1;");
      Response.Write("for(var x = 0;x < dots;x++){output += '·';}mydiv.innerText =  output;}");
      Response.Write("function StartShowWait(){mydiv.style.visibility = 'visible'; ");
      Response.Write("timeID =window.setInterval('ShowWait()',1000);}");
      Response.Write("function HideWait(){mydiv.style.visibility = 'hidden';");
      Response.Write("window.clearInterval(timeID );}");
      Response.Write("StartShowWait();</script>");
      Response.Flush();//这一句是最关键的

    }

    protected void btnUpload_Click(object sender, EventArgs e)
    {
      string startDate = txtStart.Text;
      string endDate = txtEnd.Text;
      if (!isDate(startDate) || !isDate(endDate))
      {
        lbMessage.Text = "日期格式不正确";
        return;
      }
      if (FileUpLoad1.HasFile)
      {
        string fileExtension = System.IO.Path.GetExtension(FileUpLoad1.FileName);
        if (fileExtension != ".xls" && fileExtension != ".xlsx")
        {
          lbMessage.Text = "请选择excel文件";
        }
        else
        {
          string path = Server.MapPath("~\\TZ\\WP\\File\\WPSJTZ") + fileExtension;
          FileUpLoad1.SaveAs(path);
          string conn = helpExcel.GetConn(path, fileExtension);
          string[] sheets = helpExcel.GetSheets(conn);
          for (int i = 0; i < sheets.Length; i++)
          {
            if (sheets[i] == "_xlnm#_FilterDatabase" || sheets[i] == "_xlnm#Print_Area" || sheets[i] == "_xlnm#Print_Titles")
            { continue; }
            string[] sheetName = sheets[i].Trim().Trim("'$".ToCharArray()).Split(new char[] { '-' });
            if (sheetName.Length < 2)
            { continue; }
            DataTable dt = helpExcel.GetExcelTables(sheets[i], conn);
            if (dt.Rows.Count < 5)
            { continue; }
            DataRow dr0 = dt.Rows[0];
            string f3 = dr0["F3"].ToString().Replace("井", "");
            f3 = dr0["F3"].ToString().Replace("：", " ");
            f3 = dr0["F3"].ToString().Replace(":", " ");
            string[] header = f3.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
            if (header.Length < 7)
            {
              Response.Write("sheet:" + sheets[i] + "第一行格式不正确");
              continue;
            }
            string jingdui = header[0].Trim("井队".ToCharArray());
            string jinghao = header[2];
            string jingbie = header[3] + "井";
            string jingxing = header[4] + "井";
            string jingshen = header[6];
            if (!isNumber(jingshen))
            {
              jingshen = "";
            }

            DataRow dr1 = dt.Rows[1];
            string year = dr1["F1"].ToString().Substring(0, 4);
            string dw = "HG02";
            string month = dr1["F1"].ToString().Substring(5, 2);
            if (!IsMonth(month))
            {
              lbMessage.Text = "sheet:" + sheets[i] + "月份不正确";
              return;
            }
            try
            {
              ExceuteSheet(startDate, endDate, jingdui, dw, dt, year, month, jinghao, jingbie, jingxing, jingshen);
            }
            catch
            {
              Response.Write("sheet:" + sheets[i] + "格式不正确");
              continue;
            }
          }
          lbMessage.Text = "上传成功";
        }
      }
    }

    public bool IsMonth(string month)
    {
      bool flag = false;
      System.Collections.Generic.List<string> jh = new System.Collections.Generic.List<string>();
      jh.Add("01");
      jh.Add("02");
      jh.Add("03");
      jh.Add("04");
      jh.Add("05");
      jh.Add("06");
      jh.Add("07");
      jh.Add("08");
      jh.Add("09");
      jh.Add("10");
      jh.Add("11");
      jh.Add("12");
      flag = jh.Contains(month);
      return flag;
    }

    public void ExceuteSheet(string startDate, string endDate, string jingdui, string dw, DataTable dt, string year, string month,
      string jinghao, string jingbie, string jingxing, string jingshen)
    {
      System.Text.StringBuilder sql = new System.Text.StringBuilder();
      bool isCurrentYear = false;//为去除开头几行去年数据
      //数据
      sql.Append("begin ");
      sql.Append(GetDelete(startDate, endDate, jingdui, dw, jinghao));

      for (int row = 4; row < dt.Rows.Count; row++)
      {
        DataRow dr = dt.Rows[row];
        string rq = year + "-" + month + "-" + dr["F1"].ToString();
        if (isDateTime(rq))
        {
          if (DateTime.Parse(rq).ToString("yyyyMMdd") == year + month + "01")
          { isCurrentYear = true; }
          if (!isCurrentYear)
          { continue; }
          DateTime dtNow = DateTime.Parse(rq);
          DateTime start = DateTime.Parse(startDate);
          DateTime end = DateTime.Parse(endDate);
          if (dtNow >= start && dtNow <= end)
          { sql.Append(GetInsert(dr, jingdui, dw, rq, jinghao, jingbie, jingxing, jingshen)); };
        }
      }
      sql.Append("null;end;");
      try
      {
        DbHelperOra.ExecuteSql(sql.ToString());
      }
      catch (Exception e)
      {
        Response.Write(jingdui + "-" + month + e.Message.ToString());
        Response.End();
      }
      sql.Clear();
    }

    public string GetDelete(string startDate, string endDate, string jingdui, string dw, string jinghao)
    {
      string s = "delete from TZ_WPZJSJTZ where rq>='{0}' and rq<='{1}' and dw='{3}' and jingdui='{2}' and jh='{4}';";
      return string.Format(s, startDate, endDate, jingdui, dw, jinghao);
    }

    public string GetInsert(DataRow dr, string jingdui, string dw, string rq, string jinghao,
       string jingbie, string jingxing, string jingshen)
    {
      StringBuilder strSql = new StringBuilder();
      strSql.Append("insert into TZ_WPZJSJTZ(");
      strSql.Append("RQ,DW,JH,JINGDUI,JINGXING,JINGBIE,LJJC,BRJC,ZJZSJ,SCSJHJ,SCSJCZ,SCSJFZ,SCSJTZSJXJ,SCSJTZSJGJ,SCSJTZSJCJ,FSCSJHJ,FSCSJYCSJXJ,FSCSJYCSJGZ,FSCSJYCSJFZ,FSCSJYCSJXL,FSCSJTDSJXJ,FSCSJTDSJDMDL,FSCSJTDSJQT,FZJSJHJ,FZJSJCQA,FZJSJQT,RLSJHJ,ZTBZ,SJJS)");
      strSql.Append(" values (");
      strSql.Append(@"'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}',
      '{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}');");
      return string.Format(strSql.ToString(), DateTime.Parse(rq).ToString("yyyy-MM-dd"), dw, jinghao,
        jingdui, jingxing, jingbie, dr["F2"].ToString(), dr["F3"].ToString(), TimeToMinutes(dr["F4"].ToString()),
        TimeToMinutes(dr["F5"].ToString()), TimeToMinutes(dr["F6"].ToString()), TimeToMinutes(dr["F7"].ToString()),
        TimeToMinutes(dr["F8"].ToString()), TimeToMinutes(dr["F9"].ToString()), TimeToMinutes(dr["F10"].ToString()),
        TimeToMinutes(dr["F11"].ToString()), TimeToMinutes(dr["F12"].ToString()), TimeToMinutes(dr["F13"].ToString()),
        TimeToMinutes(dr["F14"].ToString()), TimeToMinutes(dr["F15"].ToString()), TimeToMinutes(dr["F16"].ToString()),
       TimeToMinutes(dr["F17"].ToString()), TimeToMinutes(dr["F18"].ToString()), TimeToMinutes(dr["F19"].ToString()),
       TimeToMinutes(dr["F20"].ToString()), TimeToMinutes(dr["F21"].ToString()), TimeToMinutes(dr["F22"].ToString()),
       dr["F23"].ToString(), jingshen);
    }

    public string TimeToMinutes(string time)
    {
      string minutes = "";
      if (string.IsNullOrEmpty(time))
      {
        return "";
      }
      int index = time.IndexOf(':');
      if (index < 0)
      { return ""; }
      string hour = time.Substring(0, time.IndexOf(':'));
      string m = time.Substring(index + 1, time.Length - index - 1);
      TimeSpan ts;
      try
      {
        ts = new TimeSpan(int.Parse(hour), int.Parse(m), 0);
        minutes = ts.TotalMinutes.ToString();
      }
      catch
      {
        return "";
      }
      return minutes;
    }

    public bool isDate(string date)
    {
      if (string.IsNullOrEmpty(date))
        return false;
      if (date.Length != 10)
        return false;
      bool flag = true;
      try
      {
        DateTime dt = DateTime.Parse(date);
      }
      catch
      {
        flag = false;
      }
      return flag;
    }

    public bool isDateTime(string date)
    {
      bool flag = true;
      try
      {
        DateTime dt = DateTime.Parse(date);
      }
      catch
      {
        flag = false;
      }
      return flag;
    }

    public bool isNumber(string s)
    {
      bool flag = true;
      try { double d = double.Parse(s); }
      catch { flag = false; }
      return flag;
    }
  }
}